﻿import postgresql
import os
import sys

sys.argv[1]

user="palo"
password="palo"
server="sa-serv8"
db_openerp=sys.argv[1]
db_log="log"

__dir__ = os.path.dirname(os.path.abspath(__file__))
path_error = os.path.join(__dir__, "clean_"+db_openerp+"_err.log")
sys.stderr = open(path_error, 'a')

conn_db = postgresql.open("pq://"+user+":"+password+"@"+server+"/"+db_openerp)
conn_log = postgresql.open("pq://"+user+":"+password+"@"+server+"/"+db_log)

insert_into_log = conn_log.prepare("INSERT INTO oe_1s_cleaning(s_db, s_table, _statement, s_number, s_comment, s_sql) VALUES ($1, $2, $3, $4, $5, $6)")

if sys.argv[1]=="production" : 

   # удаляем договоры без подразделений

   s_comment="Договоры без подразделений"
   s_table="account_analytic_account"
   s_condition="x_1s_id IS NOT NULL AND manager_id NOT IN (SELECT resource_resource.user_id FROM resource_resource, hr_employee WHERE hr_employee.department_id IN (SELECT DISTINCT id FROM hr_department WHERE x_1s_id IS NOT NULL) AND hr_employee.resource_id=resource_resource.id)"

   delete=conn_db.prepare("DELETE FROM "+s_table+" WHERE "+s_condition)
   delete_log=delete()
   insert_into_log(db, s_table, delete_log[0], delete_log[1], s_comment, s_condition)


# удаляем сотрудников без договоров

s_comment="Cотрудники без договоров"
s_table="hr_employee"
s_condition="x_1s_sc11305_id IS NOT NULL AND resource_id IN (SELECT DISTINCT id FROM resource_resource WHERE x_1s_sc11305_id IS NOT NULL AND user_id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account))"

delete=conn_db.prepare("DELETE FROM "+s_table+" WHERE "+s_condition)
delete_log=delete()
insert_into_log(db, s_table, delete_log[0], delete_log[1], s_comment, s_condition)


# удаляем ресурсы "без договоров"

s_comment="Ресурсы без договоров"
s_table="resource_resource"
s_condition="x_1s_sc11305_id IS NOT NULL AND user_id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account)"

delete=conn_db.prepare("DELETE FROM "+s_table+" WHERE "+s_condition)
delete_log=delete()
insert_into_log(db, s_table, delete_log[0], delete_log[1], s_comment, s_condition)

# удаляем подразделения "без договоров"

s_comment="Подразделения без договоров"
s_table="hr_department"
s_condition="x_1s_id IS NOT NULL AND id NOT IN (SELECT DISTINCT department_id FROM hr_employee WHERE x_1s_sc11305_id IS NOT NULL AND resource_id NOT IN (SELECT DISTINCT id FROM resource_resource WHERE x_1s_sc11305_id IS NOT NULL AND user_id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account)))"

delete=conn_db.prepare("DELETE FROM "+s_table+" WHERE "+s_condition)
delete_log=delete()
insert_into_log(db, s_table, delete_log[0], delete_log[1], s_comment, s_condition)

# удаляем пользователей "без договоров"

s_comment="Пользователи без договоров"
s_table="res_users"
s_condition="x_1s_sc11305_id IS NOT NULL AND id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account)"

delete=conn_db.prepare("DELETE FROM "+s_table+" WHERE "+s_condition)
delete_log=delete()
insert_into_log(db, s_table, delete_log[0], delete_log[1], s_comment, s_condition)

# удаляем контрагентов-пользователей "без договоров"

s_comment="Контрагенты-пользователи без договоров"
s_table="res_partner"
s_condition="x_1s_sc11305_id IS NOT NULL AND id IN (SELECT DISTINCT partner_id FROM res_users WHERE x_1s_sc11305_id IS NOT NULL AND id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account))"

delete=conn_db.prepare("DELETE FROM "+s_table+" WHERE "+s_condition)
delete_log=delete()
insert_into_log(db, s_table, delete_log[0], delete_log[1], s_comment, s_condition)

if sys.argv[1]=="production" : 

   # удаляем контрагентов-контактов "без договоров"

   s_comment="Контрагенты-контакты без договоров"
   s_table="res_partner"
   s_condition="x_1s_id is not null AND id NOT IN (SELECT DISTINCT partner_id FROM account_analytic_account WHERE x_1s_id IS NOT NULL)"

   delete=conn_db.prepare("DELETE FROM "+s_table+" WHERE "+s_condition)
   delete_log=delete()
   insert_into_log(db, s_table, delete_log[0], delete_log[1], s_comment, s_condition)


# удаляем контрагентов-партнеров без контактов

s_comment="Контрагенты-партнеры без контактов"
s_table="res_partner"
s_condition="x_1s_name is not null AND id NOT IN (SELECT DISTINCT parent_id FROM res_partner WHERE x_1s_id IS NOT NULL)"

delete=conn_db.prepare("DELETE FROM "+s_table+" WHERE "+s_condition)
delete_log=delete()
insert_into_log(db, s_table, delete_log[0], delete_log[1], s_comment, s_condition)



sys.stderr.close()
sys.stderr = sys.__stderr__